from mysql_util import MysqlUtil
from beautifultable import BeautifulTable


def show_menu():
    print("---------------------------")
    print("      学生成绩管理系统 V1.0")
    print("                           ")
    print("      1:添加学生成绩信息")
    print("      2:查询学生成绩信息")
    print("      3:显示所有学生成绩信息")
    print("      4:删除学生成绩信息")
    print("      5:修改学生成绩信息")
    print("      6:总分成绩信息排名")
    print("      7:单科成绩信息排名")
    print("      8:查询单科成绩信息(最高分/最低分/平均分)")
    print("      0:退出系统")
    print("                           ")
    print("---------------------------")

def confirm():
    confirm = input("确认操作请输入y,否则输入n:")
    if confirm != 'y':
        return False
    else:
        return True

def print_table(column_headers=None,rows_value=None):
    table = BeautifulTable()
    table.column_headers = column_headers
    if isinstance(rows_value,list): # [{},{}]
        for student in rows_value:
            table.append_row(student.values())
    else:
        table.append_row(rows_value.values())
    print(table)

def get_student_info(student_number):
    sql = f"select {fields} from student where student_number = {student_number}"
    result = db.find(sql, fetch_one=True)
    return result

def main():
    show_menu()
    while True:
        number = int(input("请输入您的选择："))
        if number == 1: # 添加学生成绩信息
            student_number = input("请输入学号：")
            student_name = input("请输入姓名：")
            chinese = input("请输入语文成绩：")
            math = input("请输入数学成绩：")
            english = input("请输入英语成绩：")
            values = (student_number,student_name,chinese,math,english)
            sql = f'insert into student({fields}) values {values}'
            if not confirm():
                continue
            result = db.execute(sql)
            result = "添加成功" if result else "添加失败"
            print(result)
        elif number == 2: # 查询学生成绩信息
            student_number = input("请输入学号：")
            result = get_student_info(student_number)
            if result:
                print_table(fields.split(","),result)
            else:
                print("该学号不存在")
        elif number == 3: # 显示所有学生成绩信息
            sql = f"select {fields} from student"
            result = db.find(sql)
            if result: # [{},{},{}]
                print_table(fields.split(","),result)
            else:
                print("暂时没有学生信息")
        elif number == 4: # 删除学生成绩信息
            student_number = input("请输入学号：")
            if not get_student_info(student_number):
                print("该学号不存在")
                continue
            sql = f"delete from student where student_number = {student_number}"
            if not confirm():
                continue
            result = db.execute(sql)
            result = "删除成功" if result else "删除失败"
            print(result)
        elif number == 5: # 修改学生成绩信息
            student_number = input("请输入学号：")
            if not get_student_info(student_number):
                print("该学号不存在")
                continue
            update_content = input("请输入要修改的内容，多个用英文符号来区分(例如：chinese=90,math=90,english=90)：")
            update_content = update_content.replace("，",",") # 防止用户输入中文逗号
            sql = f"update student set {update_content} where student_number = {student_number}"
            if not confirm():
                continue
            result = db.execute(sql)
            result = "更改成功" if result else "更改失败"
            print(result)
        elif number == 6: # 总分成绩信息排名
            total_fields = ",".join((fields,"chinese+math+english as total"))
            order_by = "total desc"
            sql = f"select {total_fields} from student order by {order_by}"
            result = db.find(sql)
            if result: # [{},{},{}]
                table = BeautifulTable()
                table.column_headers = total_fields.split(",")
                for student in result:
                    table.append_row(student.values())
                print(table)
            else:
                print("暂时没有学生信息")
        elif number == 7: # 单科成绩信息排名
            subject = input("请输入要查询的科目,例如：chinese或者math或者english:")
            order_by = " ".join((subject,"desc"))
            sql = f"select {fields} from student order by {order_by}"
            result = db.find(sql)
            if result:  # [{},{},{}]
                print_table(fields.split(","),result)
            else:
                print("暂时没有学生信息")
        elif number == 8: # 查询单科成绩信息(最高分/最低分/平均分)
            subject = input("请输入要查询的科目,例如：chinese或者math或者english:")
            sql = f"select max({subject}) as highest,min({subject}) as lowest,avg({subject}) as average from student"
            result = db.find(sql,fetch_one=True)
            count_fields = "highest,lowest,average"
            if result:
                print_table(count_fields.split(","),result)
            else:
                print("暂时没有该科目成绩")
        elif number == 0: # 查询学生成绩信息
            print("退出成功")
            break
        else:
            print("输入无效，请按照提示输入相应的数字")
if __name__ == "__main__":
    db = MysqlUtil()
    fields = 'student_number,student_name,chinese,math,english'
    main()